package com.domainchen.framework.common.persistence;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.domainchen.framework.common.util.CollectionUtil;
import com.domainchen.framework.core.util.PropertiesLoader;

/**
 * @author domainchen or cytxiamen@163.com
 * @version 创建时间：2016年3月10日 上午12:16:35
 * @类说明 数据库操作助手类
 */
public final class DataBaseHepper {
	private static Logger log = LoggerFactory.getLogger(DataBaseHepper.class);

	private static String DRIVER;
	private static String URL;
	private static String USERNAME;
	private static String PASSWORD;

	// 1.一个线程有且仅有一个Connection
	// 2.ThreadLocal:隔离线程的容器
	private static ThreadLocal<Connection> CONNECTION_HOLDER = null;

	// QueryRunner 对象可以面向实体(Entity)进行查询
	// dbutils 对 PreparedStatement 与 ResultSet 进行封装
	/**
	 * dbutils；提供的 返回值处理方式 BeanHandler:返回Bean对象 BeanListHandler:返回List对象
	 * BeanMapHandler:返回Map对象 ArrayHandler:返回Object[]对象
	 * ArrayListHandler:返回List对象--> List<Object[]> MapHandler:返回Map对象-->Map
	 * <String,Object> MapListhandler:返回List对象-->List<Map<String,Object>>
	 * ScalarHandler:返回某列的值-->单条 ColumnListHandler:返回某列的值列表 -->多条
	 * keyedHandler:返回Map>对象,需要指定列名
	 */
	private static QueryRunner QUERY_RUNNER = null;

	private static BasicDataSource DATA_SOURCE = null;

	static {
		CONNECTION_HOLDER = new ThreadLocal<Connection>();

		QUERY_RUNNER = new QueryRunner();

		PropertiesLoader propertiesLoader = new PropertiesLoader("conf/domainchen.properties");
		DRIVER = propertiesLoader.getProperty("domainchen.framework.connection.driverClassName");
		URL = propertiesLoader.getProperty("domainchen.framework.connection.url");
		USERNAME = propertiesLoader.getProperty("domainchen.framework.connection.username");
		PASSWORD = propertiesLoader.getProperty("domainchen.framework.connection.password");

		System.err.println("DRIVER:" + DRIVER + "\nURL:" + URL + "\nUSERNAME:" + USERNAME + "\nPASSWORD:" + PASSWORD);

		DATA_SOURCE = new BasicDataSource();
		DATA_SOURCE.setDriverClassName(DRIVER);
		DATA_SOURCE.setUrl(URL);
		DATA_SOURCE.setUsername(USERNAME);
		DATA_SOURCE.setPassword(PASSWORD);
		DATA_SOURCE.setInitialSize(3);
		DATA_SOURCE.setMinIdle(3);
		DATA_SOURCE.setMaxIdle(1000);
		DATA_SOURCE.setMaxWaitMillis(6000);

	}

	/**
	 * 
	 * 获取数据库连接
	 * 
	 * @return
	 * @throws Exception
	 * @throws Exception
	 */
	public static Connection getConnection() {
		Connection conn = CONNECTION_HOLDER.get();
		try {
			if (null == conn) {
				conn = DATA_SOURCE.getConnection();
				System.out.println("连接成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("连接失败");
			log.error("DataBaseHepper--> get connection failure", e.getMessage());
		} finally {
			CONNECTION_HOLDER.set(conn);
		}

		return conn;
	}
	///////////////////////////////////////////////////////////////////////////////////////////////

	/* 操作事务 */

	///////////////////////////////////////////////////////////////////////////////////////////////

	public static void beginTransaction() {
		Connection conn = getConnection();
		if (null != conn) {
			try {
				conn.setAutoCommit(false);
			} catch (Exception e) {
				log.error("DataBaseHepper--> begin transaction failure", e.getMessage());
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.set(conn);
			}
		}
	}

	public static void commitTransaction() {
		Connection conn = getConnection();
		if (null != conn) {
			try {
				conn.commit();
			} catch (Exception e) {
				log.error("DataBaseHepper--> commit transaction failure", e.getMessage());
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}

	public static void rollBackTransaction() {
		Connection conn = getConnection();
		if (null != conn) {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
				log.error("DataBaseHepper--> rollBack transaction failure", e.getMessage());
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}
	///////////////////////////////////////////////////////////////////////////////////////////////

	/* 操作数据库数据 */

	///////////////////////////////////////////////////////////////////////////////////////////////
	/**
	 * 查询实体列表
	 * 
	 * @param enetityClass
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> List<T> queryEnityList(Class<T> enetityClass, String sql, Object... params) {
		List<T> entityList = null;
		try {
			Connection conn = getConnection();
			entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(enetityClass), params);
		} catch (Exception e) {
			log.error("DataBaseHepper-->query entity list failure", e.getMessage());
		}
		return entityList;
	}

	/**
	 * 一般用于多表
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String, Object>> queryEnityList(String sql, Object... params) {
		List<Map<String, Object>> result = null;
		try {
			Connection conn = getConnection();
			result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
		} catch (Exception e) {
			log.error("DataBaseHepper-->execute query failure", e.getMessage());
		}
		return result;
	}

	/**
	 * 查询实体
	 * 
	 * @param enetityClass
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> T queryEntity(Class<T> enetityClass, String sql, Object... params) {
		T entity = null;
		try {
			Connection conn = getConnection();
			entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(enetityClass), params);
		} catch (Exception e) {
			log.error("DataBaseHepper-->query entity list failure", e.getMessage());
		}
		return entity;
	}

	/**
	 * 插入实体
	 * 
	 * @param enetityClass
	 * @param filedMap
	 * @return
	 */
	public static <T> boolean insertEntity(Class<T> enetityClass, Map<String, Object> filedMap) {
		if (CollectionUtil.isEmpty(filedMap)) {
			log.error(" can not insert entity:fieldMap is empty");
			return false;
		}

		String sql = "INSERT INTO " + getTableName(enetityClass);
		StringBuffer columns = new StringBuffer("(");
		StringBuffer values = new StringBuffer("(");
		for (String fieldName : filedMap.keySet()) {
			columns.append(fieldName).append(", ");
			values.append("?, ");
		}
		columns.replace(columns.lastIndexOf(", "), columns.length(), ")");
		values.replace(values.lastIndexOf(", "), values.length(), ")");
		sql += columns + " VALUES " + values;
		Object[] params = filedMap.values().toArray();
		return executeUpdate(sql, params) == 1;
	}

	/**
	 * 更新实体
	 * 
	 * @param enetityClass
	 * @param id
	 * @param filedMap
	 * @return
	 */
	public static <T> boolean updateEntity(Class<T> enetityClass, int id, Map<String, Object> filedMap) {
		if (CollectionUtil.isEmpty(filedMap)) {
			log.error(" can not insert entity:fieldMap is empty");
			return false;
		}

		String sql = "UPDATE " + getTableName(enetityClass) + " SET ";
		StringBuffer columns = new StringBuffer("");
		for (String fieldName : filedMap.keySet()) {
			columns.append(fieldName).append("=?, ");
		}
		sql += columns.substring(0, columns.lastIndexOf(", ")) + " WHERE id=?";
		List<Object> paramList = new ArrayList<Object>();
		paramList.addAll(filedMap.values());
		paramList.add(id);
		Object[] params = paramList.toArray();

		return executeUpdate(sql, params) == 1;
	}

	/**
	 * 删除实体
	 * 
	 * @param enetityClass
	 * @param id
	 * @return
	 */
	public static <T> boolean deleteEntity(Class<T> enetityClass, int id) {
		String sql = "DELETE FROM " + getTableName(enetityClass) + " WHERE id=?";
		return executeUpdate(sql, id) == 1;
	}

	public static void executeSqlFile(String filePath) {
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
		BufferedReader reader = new BufferedReader(new InputStreamReader(is));
		try {
			String sql = "";
			while ((sql = reader.readLine()) != null) {
				executeUpdate(sql);
			}
		} catch (Exception e) {
			log.error("DataBaseHepper-->execute sql file failure", e.getMessage());
			throw new RuntimeException(e);
		}
	}

	/**
	 * 执行更新语句(包括 update /insert /delete) 通用
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int executeUpdate(String sql, Object... params) {
		int rows = 0;
		try {
			Connection conn = getConnection();
			rows = QUERY_RUNNER.update(conn, sql, params);
		} catch (Exception e) {
			log.error("DataBaseHepper-->execute update  failure", e.getMessage());
		}
		return rows;
	}

	private static String getTableName(Class<?> entityClass) {
		return entityClass.getSimpleName();
	}
}
